package com.dy.yunying.biz.dao.znfx;

import com.dy.yunying.api.req.FutureMaterialReq;
import com.dy.yunying.api.vo.FutureDaoVo;
import com.dy.yunying.biz.config.YunYingProperties;
import com.dy.yunying.biz.utils.DateUtils;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.time.format.DateTimeFormatterBuilder;
import java.util.List;
import java.util.Objects;

/**
 * 素材管理-数据报表
 * @author chenxiang
 * @className AdMaterialDataDao
 * @date 2023-2-27 15:06
 */
@Slf4j
@Component
@RequiredArgsConstructor
public class FutureMaterialDataDao {

	@Resource(name = "clickDcSessionTemplate")
	private JdbcTemplate clickhouseTemplate;
	private static final DateTimeFormatter DATE_FORMATTER = new DateTimeFormatterBuilder().appendPattern(DateUtils.YYYYMMDD).toFormatter();
	private final YunYingProperties prop;

	public int futureCount(FutureMaterialReq req){
		StringBuffer sqlBuilder = new StringBuffer();
		req.setEdate(LocalDate.now().format(DATE_FORMATTER));
		req.setSdate(LocalDate.now().minusDays(prop.getFutureTime()).format(DATE_FORMATTER));
		this.futureDataSql(sqlBuilder,req);
		final String sql = sqlBuilder.toString();
		log.debug("潜力素材总数{} SQL: [\n{}]",req.getFutureType(), sql);
		long start = System.currentTimeMillis();
		final List<FutureDaoVo> list = clickhouseTemplate.query(sql, new Object[]{}, new BeanPropertyRowMapper<>(FutureDaoVo.class));
		long end = System.currentTimeMillis();
		log.info("潜力素材总数{} 耗时: {}ms",req.getFutureType(), end - start);
		return list.size();
	}


	/**
	 * 查询数据报表
	 * @return
	 */
	public List<FutureDaoVo> futureMaterial(FutureMaterialReq req) {
		StringBuffer sqlBuilder = new StringBuffer();
		req.setEdate(LocalDate.now().format(DATE_FORMATTER));
		req.setSdate(LocalDate.now().minusDays(prop.getFutureTime()).format(DATE_FORMATTER));
		this.futureDataSql(sqlBuilder,req);
		sqlBuilder.append(" ORDER BY value DESC").append(" \n");
		Long current = req.getCurrent();
		Long size = req.getSize();
		if (Objects.nonNull(current) && Objects.nonNull(size)) {
			Long offset = (current - 1) * size;
			sqlBuilder.append("LIMIT\n");
			sqlBuilder.append("    ").append(offset).append(", ").append(size).append('\n');
		}
		final String sql = sqlBuilder.toString();
		log.debug("潜力素材{} SQL: [\n{}\n]",req.getFutureType(), sql);
		long start = System.currentTimeMillis();
		final List<FutureDaoVo> list = clickhouseTemplate.query(sql, new Object[]{}, new BeanPropertyRowMapper<>(FutureDaoVo.class));
		long end = System.currentTimeMillis();
		log.info("潜力素材{} 耗时: {}ms",req.getFutureType(), end - start);
		return list;
	}

	private void futureDataSql(StringBuffer sql, FutureMaterialReq material) {
		sql.append(" SELECT ").append(" \n");
		sql.append("     cid, -- 对应头条体验素材id ").append(" \n");
		sql.append("     cost, -- 返点后消耗 ").append(" \n");
		switch (material.getFutureType()){
			case 0:
				sql.append("     toDecimal64(IF(registerNum > 0, round(cost / IF(registerNum > 0, registerNum, 1), 2), 0), 2) AS value  ").append(" \n");
				break;
			case 1:
				sql.append("     round(IF(toInt64(usrpaynamenums) > 0, divide(toFloat64(cost), usrpaynamenums), 0), 2) AS value ").append(" \n");
				break;
			case 2:
				sql.append("     round(if(cost > 0,divide(toFloat64(day1DevicesharFee) * 100.00,cost),0),2) value ").append(" \n");
				break;
			case 3:
				sql.append("     toDecimal64(IF(cost > 0, round(totalPayFee * 100.00 / cost, 1), 0), 2) AS value ").append(" \n");
				break;
			default:
				sql.append("     '0' AS value ").append(" \n");
				break;
		}
		sql.append(" FROM ").append(" \n");
		sql.append("     ( ").append(" \n");
		sql.append("         SELECT ").append(" \n");
		sql.append("          sd.material_id   cid, -- 对应头条体验素材id ").append(" \n");
		switch (material.getFutureType()){
			case 0:
				sql.append("             SUM(IF(LENGTH(latest_username) > 0, 1, 0)) AS registerNum ").append(" \n");
				break;
			case 1:
				sql.append("             COALESCE(SUM(IF(reg.fee_1 > 0 OR reg.givemoney_1 > 0, 1, 0)), 0) usrpaynamenums ").append(" \n");
				break;
			case 2:
				sql.append("             COALESCE(SUM(reg.fee_1 * sharing),0) day1DevicesharFee ").append(" \n");
				break;
			case 3:
				sql.append("             COALESCE(SUM(reg.fee_total * sharing),0) AS totalPayFee ").append(" \n");
				break;
			default:
				sql.append("             COALESCE(SUM(reg.fee_1 * sharing),0) day1DevicesharFee ").append(" \n");
				break;
		}
		sql.append("         FROM ").append(" \n");
		sql.append("             ( ").append(" \n");
		sql.append("                 SELECT ").append(" \n");
		switch (material.getFutureType()){
			case 0:
				sql.append("     latest_username  ").append(" \n");
				break;
			case 1:
				sql.append("     fee_1, givemoney_1 ").append(" \n");
				break;
			case 2:
				sql.append("     fee_1 ").append(" \n");
				break;
			case 3:
				sql.append("     fee_total ").append(" \n");
				break;
			default:
				sql.append("     fee_1 ").append(" \n");
				break;
		}
		sql.append("                     ,if(mid3 <> '',mid3, cid) sdd,game_main pgid ").append(" \n");
		sql.append("                 FROM ").append(" \n");
		sql.append("                     ").append(prop.getNinetydeviceregtable()).append(" \n");
		sql.append("                 WHERE ").append(" \n");
		sql.append("                      spread_type = 1 AND reg_day >= ").append(material.getSdate()).append(" AND reg_day <= ").append(material.getEdate()).append(" \n");
		sql.append("             ) reg ").append(" \n");
		sql.append("             LEFT JOIN dim_200_pangu_mysql_ad_creative_material sd on CAST(sd.creative_id AS String) = CAST(reg.sdd AS String) ").append(" \n");
		sql.append("             LEFT JOIN (SELECT CAST(id AS Int16) AS id,(1 - COALESCE(sharing,0)) sharing FROM dim_200_pangu_mysql_parent_game pg_tmp) pg ON reg.pgid = pg.id ").append(" \n");
		sql.append("         GROUP BY sd.material_id").append(" \n");
		sql.append("     ) a ").append(" \n");
		sql.append("    FULL JOIN (  SELECT  cid, -- 素材id    ").append(" \n");
		sql.append("      toFloat64(COALESCE(SUM(cost),0)) cost -- 返点后消耗   ").append(" \n");
		sql.append("      FROM ").append(" \n");
		sql.append("       ( SELECT  sd.material_id cid,cost,day FROM ").append(" \n");
		sql.append("       (SELECT CAST(material_id AS String) cid ,cost, day FROM ").append(prop.getMaterialDayReport()).append(" \n");
		sql.append("       WHERE ").append(" \n");
		sql.append("       day >= ").append(material.getSdate()).append(" AND day <= ").append(material.getEdate()).append(" \n");
		sql.append("       UNION ALL  ").append(" \n");
		sql.append("      SELECT creative_id cid,cost, day FROM ").append(prop.getCreativeDayReport()).append(" \n");
		sql.append("       WHERE ").append(" \n");
		sql.append("       day >= ").append(material.getSdate()).append(" AND day <= ").append(material.getEdate()).append(" \n");
		sql.append("       ) n ").append(" \n");
		sql.append("    LEFT JOIN dim_200_pangu_mysql_ad_creative_material sd on CAST(sd.creative_id AS String) = CAST(n.cid AS String)  ").append(" \n");
		sql.append("     ) GROUP BY cid   ").append(" \n");
		sql.append("     )  reg USING (cid) ").append(" \n");
		sql.append("       WHERE cost>").append(prop.getFutureMinCost()).append(" and cost<").append(prop.getFutureMaxCost()).append(" ").append(" \n");
		sql.append("   and value ");
		switch (material.getFutureType()){
			case 0:
				sql.append(" < '").append(prop.getFutureRegCost()).append("' \n");
				break;
			case 1:
				sql.append(" < '").append(prop.getFuturePayCost()).append("' \n");
				break;
			case 2:
				sql.append(" > '").append(prop.getFutureFirstRoi()).append("' \n");
				break;
			case 3:
				sql.append(" > '").append(prop.getFutureTotalRoi()).append("' \n");
				break;
			default:
				sql.append(" <> '").append(0).append("' \n");
				break;
		}

	}



}
